package cn.shiro.shiroservice.jdbc;

import cn.shiro.shiroservice.common.utils.SqlSessionUtils;
import cn.shiro.shiroservice.moudle.db.mapper.DataBaseMapper;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

/**
 * &#064;Time 2024 一月 星期二 23:21
 *
 * @author ShangGuan
 */
@SpringBootTest
public class BackUpTables {

    @Test
    public void baskUp(){

    }
    public static void main(String[] args) {
        // JDBC 连接信息
        String jdbcUrl = "jdbc:mysql://localhost:3306/shiro_service_db?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
        String username = "root";
        String password = "12345678";

        // SQL 查询语句（备份整个表）
        String tableName = "sys_role";
        String selectQuery = "SELECT * FROM " + tableName;
        try {
            // 建立 JDBC 连接
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
            Statement statement = connection.createStatement();
            // 执行查询
            ResultSet resultSet = statement.executeQuery(selectQuery);
            // 获取查询结果并写入文件
            writeResultSetToFile(resultSet, tableName + "_backup.sql");
            // 关闭资源
            resultSet.close();
            statement.close();
            connection.close();
            System.out.println("Backup completed !");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 将查询结果写入文件
    private static void writeResultSetToFile(ResultSet resultSet, String fileName) throws IOException, SQLException {
        try (PrintWriter writer = new PrintWriter(new FileWriter(fileName))) {
            while (resultSet.next()) {
                // 每一行数据以 INSERT 语句的形式写入文件
                StringBuilder insertStatement = new StringBuilder("INSERT INTO your_table (");

                // 获取列名
                for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
                    insertStatement.append(resultSet.getMetaData().getColumnName(i));
                    if (i < resultSet.getMetaData().getColumnCount()) {
                        insertStatement.append(", ");
                    }
                }
                insertStatement.append(") VALUES (");
                // 获取列值
                for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
                    insertStatement.append("\"").append(resultSet.getString(i)).append("\"");
                    if (i < resultSet.getMetaData().getColumnCount()) {
                        insertStatement.append(", ");
                    }
                }
                insertStatement.append(");");
                writer.println(insertStatement);
            }
        }
    }


}
